<?php
/**
 * @Sun May 10 CST 2009
 * MySql数据库操作 Db类
 */
class Db{
	public  $debug;				//是否启用调试信息		@var boolean true启用，false不启用
	public  $mysqli;			//是否启用调试信息		@var boolean true启用，false不启用
	private $queries;			//sql语句数组
	private $query_id;
	private $database;
	private $query_log;
	public  $is_connect;
	private $transaction;		//是否开启事务			@var boolean true开启，false默认关闭
	private $query_count;		//sql查询次数			@var int
	private $arr_sum_time;		//sql总共消耗时间 		@var int
	private $arr_sum_memory;	//sql总共消耗内存		@var int
	private $shutdown_queries;	//sql语句数组，页面输出完毕后被执行
	
	/**
	 * Db类初始化
	 * 把 closeDb()方法放入register_shutdown_function() 中执行 (页面输出完毕，执行close_db())<br/>
	 * q_arr query执行sql并缓存查询结果，row_query_unbuffered执行sql不缓存查询结果，row_query_shutdown页面输出完毕再执行sql语句
	 */
	function __construct() {
		$this->debug			= DEBUG;
		$this->queries			= array();
		$this->query_log		= array();
		$this->is_connect		= false;
		$this->query_count		= 0;	
		$this->transaction		= false;
		$this->arr_sum_time		= array();
		$this->arr_sum_memory	= array();
		$this->shutdown_queries	= array();
		register_shutdown_function(array($this, "closeDb"));
		/*include('Config/db_config.php');		
		$this->connect($db_config);*/
	}
	
	/**
	 * connect	MySql DataBase
	 * @param	array 数据库参数 hostname, username, password, database, charset
	 * @return	boolean 是否连接mysql
	 */
	function connect($db_config){
		$this->database	= $db_config['database'];
		$this->mysqli	= new mysqli($db_config["hostname"], $db_config["username"], $db_config["password"], $db_config["database"]);
		if ($this->mysqli){
			$this->is_connect= true;
			if ($db_config["charset"]) {
				$this->mysqli->query("SET NAMES '".$db_config["charset"]."'");
			}
		}
		return $this->mysqli;
	}
	
	/**
	 * 新增加一条记录
	 * @param	string $tab 表名
	 * @param	array $row 被插入数据 键(字段) => 值(数据)
	 * @return	boolean $q_num=0时，返回getID()
	 */
	function insert($tab, $row){
		$row = addslashes_str($row);
		$sql = $this->sqlInsert($tab, $row);
		$this->queryUnbuffered($sql);
		return $this->getID();
	}
	
	/**
	 * @return int INSERT 操作产生的 ID
	 */
    function getID(){
        return $this->mysqli->insert_id;
    }
    
    /**
	 * 删除指定记录
	 * @param string $tab 表名
	 * @param string $where 更新条件
	 * @return boolean sql语句是否执行成功，判断delete所影响的记录数，用getAffectedRows
	 */
	function delete($tab, $where){
		$sql = $this->sqlDelete($tab, $where);
		return $this->queryUnbuffered($sql);
	}
    
    /**
	 * 更新指定记录
	 * @param string $tab 表名
	 * @param array $row 被更新数据 键(字段) => 值(数据)
	 * @param string $where 更新条件
	 * @return boolean sql语句是否执行成功，判断update所影响的记录数，用affected_rows()
	 */
	function update($tab, $row, $where){
		$row = addslashes_str($row);
		$sql = $this->sqlUpdate($tab, $row, $where);
		return $this->queryUnbuffered($sql);
	}
	
	/**
	 * 根据条件查询，返回所有记录
	 * @param string $tab 表名
	 * @param string $where 查询条件
	 * @param int $limit 返回记录条数
	 * @param string $fields 返回字段
	 * @param string $orderby 排序字段
	 * @param string $sort DESC/ASC 排列顺序
	 * @return array 返回所查询的数据记录
	 */
	function fetchAll($tab, $where='', $limit=0, $fields='*', $orderby='id', $sort='DESC'){
		$sql = $this->sqlSelect($tab, $where, $limit, $fields, $orderby, $sort);
		return $this->fetchRowAll($sql);
	}
	
	/**
	 * 根据where条件，查询查询一条数据库记录
	 * @param string $tab 表名
	 * @param string $where 查询条件
	 * @param string $fields 返回字段
	 * @return array 返回所查询的一条数据记录
	 */
	function fetchOne($tab, $where, $fields='*', $orderby='id', $sort='DESC'){
		$sql = $this->sqlSelect($tab, $where, 1, $fields, $orderby, $sort);
		return $this->fetchRowOne($sql);
	}
	
	/**
	 * 执行sql语句，查询结果是多条数据
	 * @return array 查询结果
	 */
	function fetchRowAll($sql){
		$rs	 = $this->query($sql);
		$rs_num = $this->getNumRows($rs);
		$rows = array();
		for($i=0; $i<$rs_num; $i++){
			$rows[] = $this->_fetchArray($rs);
		}
		$this->_freeResult($rs);
		$row = stripslashes_str($row);
		return $rows;
	}
	
	/**
	 * 执行sql语句，查询结果是一条数据
	 * @return array 查询结果
	 */
	function fetchRowOne($sql){
		$rs	 = $this->query($sql);
		$row = $this->_fetchArray($rs);
		$this->_freeResult($rs);
		$row = stripslashes_str($row);
		return $row;
	}
	
	/**
	 * 执行一组sql语句，获取所有结果
	 * @param string $sql 一组以;号分割的sql
	 * @return array
	 */
	function rowQueryMulti($sql){
		$row = array();
		if ($this->mysqli->multi_query($sql)) {
		    do {
		        if ($rs = $this->mysqli->store_result()) {
		            $rs_num = $this->getNumRows($rs);
					for($i=0; $i<$rs_num; $i++){
						$row[] = $this->_fetchArray($rs);
					}
		            $this->_freeResult($rs);
		        }
		    } while ($this->mysqli->next_result());
		}
		$row = stripslashes_str($row);
		return $row;
	}
	
	/**
	 * 发送sql查询，并不获取和缓存结果的行
	 * @param string 一条完整的sql语句
	 * @return boolean 是否执行成功
	 */
	function queryUnbuffered($sql){
		return $this->query($sql, 0);
	}
	
	/**
	 * 把sql放入数组中，页面输出完毕后，执行sql语句
	 * @param string 一条完整的sql语句
	 * @return void
	 */
	function queryShutdown($sql){
		$this->shutdown_queries[] = $sql;
    }
    
	/**
	 * 取得一条sql语句对数据库记录的影响条数，仅对 INSERT，UPDATE 或者 DELETE 有效
	 * @return int 影响条数
	 */
	function getAffectedRows() {
        return $this->mysqli->affected_rows;
    }
    
    /**
	 * 某表，某字段，加减 N
	 * @param string $tab 表名
	 * @param string $field 字段名
	 * @param +,- $op 运算符
	 * @param int $num 被加、减的数值
	 * @param string $where 修改条件
	 * @return boolean sql语句是否执行成功，判断delete所影响的记录数，用getAffectedRows
	 */
	function updateOp($tab, $field, $op, $where, $num=1){
		$sql = "UPDATE `$tab` SET $field=$field $op $num WHERE ".$where;
		return $this->queryUnbuffered($sql);
	}
	
	/**
	 * 根据条件对某个表做总数统计
	 * @param string $tab 表名
	 * @param string $where 统计条件
	 * @param string $auto_id 主键id
	 * @return int 记录总数
	 */
	function getCount($tab, $where="", $auto_id='*'){
		$sql = 'SELECT count('.$auto_id.') as row_sum FROM `'.$tab.'` '.($where?' WHERE '.$where:'');
		$row = $this->fetchRowOne($sql);
		return $row['row_sum'];
	}
	
	/**
	 * 关闭 MySql 连接
	 * @return boolean 是否成功关闭mysql连接
	 */
    function closeDb(){
    	$bool = false;
    	if (isset($this->mysqli) && is_object($this->mysqli)){
    		$this->_shutdownDb();
    		echo $this->_dbDebug();
			$this->queries = array();
			$this->shutdown_queries = array();
    		$bool = $this->mysqli->close();
    		unset($this->mysqli);
    	}
    	return $bool;
    }
    
    /**
	 * 获取数据库中所有表名
	 * @return array 数据库中所有表名
	 */
	function getAllTables(){
		$sql = 'SHOW TABLES';
		$row = $this->rowQuery($sql);
		$arr = array();
		foreach ($row AS $v) {
			$arr[] = reset($v);
		}
		return $arr;
   	}
   	
   	/**
	 * 取得查询记录总数，仅对 SELECT 语句有效
	 * @return int 查询记录总数
	 */
    private function getNumRows($result) {
        return $result->num_rows;
    }
     
	private function sqlInsert($tab, $row){
		$sqlfield = '';
		$sqlvalue = '';
		foreach ($row as $key=>$value) {
			$sqlfield .= $key.",";
			$sqlvalue .= "'".$value."',";
		}
		return "INSERT INTO `".$tab."`(".substr($sqlfield, 0, -1).") VALUES (".substr($sqlvalue, 0, -1).")";
	}
	
	private function sqlDelete($tab, $where){
		return "DELETE FROM `".$tab."` WHERE ".$where;
	}
	
	private function sqlUpdate($tab, $row, $where){
		$sqlud = '';
		foreach ($row as $key=>$value) {
			$sqlud .= $key."= '".$value."',";
		}
		return "UPDATE `".$tab."` SET ".substr($sqlud, 0, -1)." WHERE ".$where;
	}
	
	private function sqlSelect($tab, $where="", $limit=0, $fields="*", $orderby="id", $sort="DESC"){
		$sql = "SELECT ".$fields." FROM `".$tab."` ".($where?" WHERE ".$where:"");
		$sql.= $limit==1 ? '' : ' ORDER BY '.$orderby." ".$sort.($limit ? " limit ".$limit:"");
		return $sql;
	}
	
	/**
	 * 输出此刻的毫秒时间
	 * @return float
	 */
	private function _getMicrotime(){
		list($usec, $sec) = explode(" ", microtime());
		return ((float)$usec + (float)$sec);
	}
	
	/**
	 * 释放结果内存
	 */
	private function _freeResult($rs){
		$rs->free_result();
	}
	
	/**
	 * 从结果集中取得一行作为关联数组
	 * @param string $result 查询结果集
	 * @return array 一条数据库记录
	 */
    private function _fetchArray($rs=''){
        return $rs->fetch_array(MYSQLI_ASSOC);
    }
    
    /**
	 * 执行shutdown_queries数组中的sql语句
	 */
	private function _shutdownDb() {
		foreach((array)$this->shutdown_queries as $sql) {
			$this->queryUnbuffered($sql);
		}
	}
	
	/**
	 * 返回上一个 MySQL 操作中的错误信息的数字编码
	 * @return int 错误信息的数字编码
	 */
	private function _getErrno(){
		$this->errno = $this->mysqli->errno;
		return $this->errno;
	}
	
	/**
	 * MYSQL Debug 调试
	 * @global int $TIME_START 页面初始时间
	 * @return string 调试信息
	 */
	private function _dbDebug(){
		global $TIME_START;
		$str = '';
		if($this->debug==true){
			$TIME_END = $this->_getMicrotime();
			$arr_include = get_included_files();
			$str = '<div style="clear:both;margin:10px auto;width:90%;text-align:center;font-size:13px;">';
			$str.= "SQL查询次数：".$this->query_count;
			$str.= " &nbsp;&nbsp; 耗时：".round(array_sum($this->arr_sum_time), 4).'秒';
			$str.= " &nbsp;&nbsp; 占用内存：".round(array_sum($this->arr_sum_memory)/1024, 4).'KB';
			$str.= " &nbsp;&nbsp; 页面执行时间：".round(($TIME_END - $TIME_START), 4).'秒';
			$str.= ' &nbsp;&nbsp; 加载 include '.count($arr_include).'个文件';
			$str.= "</div>"."\n";
			$str.= '<pre>';
			foreach ((array)$this->queries AS $v){
				$str.= $v."\n";
			}
			$str.= "------shutdown querys-----\n";
			foreach ((array)$this->shutdown_queries as $v){
				$str.= $v."\n";
			}
			$str.= "</pre>";
			$str.= "<br/><hr width='80%' /><br/>";
			foreach ((array)$this->query_log AS $v){
				$str.= $v."\n";
			}
			$str.= '<pre>';
			foreach ((array)$arr_include as $v) {
				$str.= $v."\n";
			}
			$str.= "</pre>";
		}
		return $str;
	}
	
	/**
	 * 执行sql语句，并且返回结果集
	 * @param string $sql sql语句
	 * @param 0,1 $query_type 0查询不缓存结果， 1正常查询
	 * @return boolean 成功时返回一个资源标识符，失败时返回false
	 */
	function query($sql, $query_type='1'){
		if ($this->debug==true) {
			$str = '<pre style="margin:10px;">查询SQL'.($query_type == 0 ? ' (<strong style="color:red;">unbuffered</strong>)' : '').":\n".htmlspecialchars($sql)."\n";
			if (function_exists('memory_get_usage')) {
				$memory_before = memory_get_usage();
			}
			$this->queries[] = $sql;
		}
		$before_time	= $this->_getMicrotime();
		if ($query_type == 0) {
			$rs = $this->mysqli->query($sql, MYSQLI_USE_RESULT);
		}else{
			$rs = $this->mysqli->query($sql);
		}
		$after_time	= $this->_getMicrotime();
        
		$this->query_count++;
		if ($this->debug==true) {
			$sql_time = $after_time - $before_time;
			$this->arr_sum_time[] = $sql_time;
			$str.= "SQL执行时间: " . $sql_time . "\n";
			if (function_exists('memory_get_usage')) {
				$memory_after = memory_get_usage();
				$sql_memory = $memory_after - $memory_before;
				$this->arr_sum_memory[] = $sql_memory;
				$str.= "内存占用: " .round($sql_memory/1024, 4).'KB'."\n";
			}
			$str.= "</pre>";
			if (preg_match('#(^|\s)SELECT\s+#si', $sql)) {
				$explain_id = $this->mysqli->query("EXPLAIN " . $sql);
				$str.= '
				<table width="80%" border="1" style="border-collapse:collapse;text-align:center;" cellpadding="0" cellspacing="0" bordercolor="#000000" align="center">
				<tr height="25" style="font-weight:bold">
					<td>table</td>
					<td>type</td>
					<td>possible_keys</td>
					<td>key</td>
					<td>key_len</td>
					<td>ref</td>
					<td>rows</td>
					<td>Extra</td>
				</tr>
				';
				$str_tmp = "";
				while($array = $explain_id->fetch_array()) {
					$str_tmp.= "
					<tr height='25'>
						<td>$array[table]</td>
						<td>$array[type]</td>
						<td>$array[possible_keys]</td>
						<td>$array[key]</td>
						<td>$array[key_len]</td>
						<td>$array[ref]</td>
						<td>$array[rows]</td>
						<td>$array[Extra]</td>
					</tr>
					";
				}
				$str.= $str_tmp."</table>\n<br /><hr width='80%' />\n";
			}else{
				$str.= "\n<hr width='80%' />\n\n";
			}
			$this->query_log[] = $str;
		}
        return $rs;
	}
	
	function begin(){
		$this->queryUnbuffered('BEGIN');
		$this->transaction = 1;
	}
	
	function commit(){
		if ($this->transaction) {
			$this->queryUnbuffered('COMMIT');
			$this->transaction = 0;
		}
	}
	
	function savePoint($point){
		$this->queryUnbuffered('SAVEPOINT '.$point);
	}
	
	function rollback($point=''){
		if ($this->transaction) {
			$this->queryUnbuffered('ROLLBACK'.($point ? ' TO SAVEPOINT '.$point : ''));
			$this->transaction = 0;
		}
	}
	
	function __destruct(){
		$this->closeDb();
	}
}
?>